﻿-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[proc_Statistics_Financial_SelectDaily]
	-- Add the parameters for the stored procedure here
	(@CompanyId int)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering ;WITH SELECT statements.
	--SET NOCOUNT ON;


	SELECT 
--现金收入
ISNULL(( SELECT SUM([Income]) FROM [Finance_RunningAccount] WHERE  PaymentTypeId=1  
 AND CONVERT(VARCHAR(10), createdate,120)=CONVERT(VARCHAR(10), GETDATE(),120)   AND CompanyId=@CompanyId),0)-
 --现金支出
 ISNULL((SELECT sum(outlayprice)
  FROM [Finance_Outlay] 
WHERE  PaymentTypeId=1 and CONVERT(VARCHAR(10), createdate,120)=CONVERT(VARCHAR(10), GETDATE(),120)   AND CompanyId=@CompanyId),0)+
--昨日现金
ISNULL((select IBA FROM Statistics_Financial  WHERE
 CONVERT(VARCHAR(10), createdate,120)=CONVERT(VARCHAR(10),DATEADD(D,-1, GETDATE()),120)   AND CompanyId=@CompanyId),0)
  AS iba
, 
--银行收入
ISNULL(( SELECT SUM(income) FROM [Finance_RunningAccount] WHERE PaymentTypeId=2
  AND CONVERT(VARCHAR(10), createdate,120)=CONVERT(VARCHAR(10), GETDATE(),120)   AND CompanyId=@CompanyId),0)-
--银行支出
ISNULL((SELECT sum(outlayprice)
  FROM [Finance_Outlay] 
WHERE  PaymentTypeId=2 and CONVERT(VARCHAR(10), createdate,120)=CONVERT(VARCHAR(10), GETDATE(),120)   AND CompanyId=@CompanyId),0)+
--昨日银行
ISNULL((select IBB FROM Statistics_Financial  WHERE
 CONVERT(VARCHAR(10), createdate,120)=CONVERT(VARCHAR(10),DATEADD(D,-1, GETDATE()),120)   AND CompanyId=@CompanyId),0)
	AS ibb,

isnull((SELECT sum(a) AS zye FROM (
			 SELECT (SELECT TOP 1 ye FROM AB_list al WHERE al.Iid=AB_list.Iid ORDER BY al.UpTime desc) AS a
			 FROM AB_list 
			 WHERE Iid IN(SELECT id FROM AB_Interface WHERE syzh<>0 AND CompanyId=@CompanyId)
			 AND  Convert(varchar(10),UpTime,120)<=CONVERT(VARCHAR(10), GETDATE(),120)  AND AB_Type=0 GROUP BY Iid	
			 ) AS nb),0) AS zye

END
